package com.tfkj.repository;

import com.tfkj.entity.Question;
import com.tfkj.entity.QuestionType;
import com.tfkj.entity.Suggestion;
import com.tfkj.vo.QuestionSuggestionVO;
import org.apache.ibatis.annotations.*;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;


@Mapper
public interface QuestionRepository {


    @Select("<script>" +
            " select a.* from schooltest.question a where 1=1" +
            " <if test='grade != null'>" +
            "  AND grade = #{grade} " +
            " </if>" +
            " <if test='subject != null'>" +
            "  AND subject = #{subject} " +
            " </if>" +
            "  <if test='questionTypes != null and !questionTypes.isEmpty()'>" +
            "    AND (" +
            "    <foreach item='type' collection='questionTypes' separator=' OR '>" +
            "      CONCAT(',', questionType, ',') LIKE CONCAT('%,', #{type}, ',%')" +
            "    </foreach>)" +
            "  </if>" +
            " <if test='difficulty != null'>" +
            "  AND a.difficulty = ${difficulty} " +
            " </if>" +
            " order by id " +
            " limit ${pageSize} offset ${offset}"+
            "</script>")
    List<Question> findQuestions(@Param("grade") String  grade,@Param("subject") String   subject, @Param("questionTypes") List<String> questionTypes,  @Param("difficulty")Integer difficulty, @Param("offset") Integer offset, @Param("pageSize") Integer pageSize);

    @Select("<script>" +
            " select COUNT(*) from schooltest.question a where 1=1" +
            " <if test='grade != null'>" +
            "  AND grade = #{grade}" +
            " </if>" +
            " <if test='subject != null'>" +
            "  AND subject = #{subject}" +
            " </if>" +
            "  <if test='questionTypes != null and !questionTypes.isEmpty()'>" +
            "    AND (" +
            "    <foreach item='type' collection='questionTypes' separator=' OR '>" +
            "      CONCAT(',', questionType, ',') LIKE CONCAT('%,', #{type}, ',%')" +
            "    </foreach>)" +
            "  </if>" +
            " <if test='difficulty != null'>" +
            "  AND a.difficulty = ${difficulty} " +
            " </if>" +
            " order by id" +
            "</script>")
    Integer findQuestionsTotal(@Param("grade") String  grade,@Param("subject") String   subject,@Param("questionTypes") List<String> questionTypes,  @Param("difficulty")Integer difficulty);


    @Insert("insert into schooltest.question(testId, url) values(${testId}, #{url})")
    Integer insertQuestion(@Param("testId")Integer testId, @Param("url") String url);

    @Select("select * from schooltest.question_type")
    List<QuestionType> findQuestionsType();

    @Delete("delete schooltest.question where id = ${id}")
    void deleteById(@Param("id") Integer id);


    @Delete("delete from schooltest.question_type where `id` = ${id}")
    void deleteType(@Param("id") Integer id);

    @Insert("insert into schooltest.question_type(grade, subject, questionType) values(#{grade}, #{subject}, #{questionType})")
    void createType(@Param("grade") String grade, @Param("subject") String subject, @Param("questionType") String questionType);


    @Select("select distinct grade from schooltest.question_type order by id")
    List<String> findGrades();

    @Select("select distinct subject from schooltest.question_type where grade = #{grade} order by id")
    List<String> findSubject(@Param("grade")String grade);

    @Select("""
                select a.*
                FROM schooltest.question a
                WHERE a.grade = #{grade}
                AND a.subject = #{subject}
                AND a.questionType IS NULL
                limit ${pageSize} offset ${offset}  
            """)
    List<Question> findQuestionsWithSubject(@Param("grade") String grade, @Param("subject") String subject, @Param("offset") Integer offset, @Param("pageSize") Integer pageSize);

    @Select("""
                SELECT COUNT(*)
                FROM schooltest.question a
                WHERE a.grade = #{grade}
                AND a.subject = #{subject}
                AND a.questionType IS NULL
                AND a.questionType IS NULL              
            """)
    Integer findQuestionsWithSubjectTotal(String grade, String subject);

    @Select("<script>\n" +
            " select distinct id, questionType from schooltest.question_type where 1=1" +
            " <if test='grade != null'>" +
            "  AND grade = #{grade}" +
            " </if>" +
            " <if test='subject != null'>" +
            "  AND subject = #{subject}" +
            " </if>" +
            " order by id"+
            "</script>")
    List<QuestionType> findQuestionType(@Param("grade") String grade, @Param("subject") String subject);
    @Select("select distinct id, questionType from schooltest.question_type where grade = #{grade} order by id")
    List<QuestionType> findQuestionTypeWithoutSubject(@Param("grade") String grade);

    @Select("""
                <script>
                select COUNT(*)
                FROM schooltest.question a
                LEFT JOIN schooltest.test b
                ON a.testId = b.id
                WHERE b.grade = #{grade}
                AND b.subject = #{subject}
                <if test='difficulty != null'>
                 AND a.difficulty = ${difficulty}
                </if>
                AND a.questionType IS NOT NULL
                </script>           
            """)
    Integer findQuestionWithSubjectAndDiffTotal(@Param("grade") String grade, @Param("subject") String subject, @Param("difficulty") Integer difficulty);

    @Select("select url from schooltest.question where id in ${ids}")
    List<String> findUrlsById(String ids);

    @Select("select id, url from schooltest.question where id in ${ids}")
    List<Question> findUrlsAndIdById(String ids);

    @Insert("insert into schooltest.suggestion(teacherNum, questionIds, studentIds) values(#{teacherNum}, #{questionIds}, #{studentIds})")
    void suggestion(@Param("teacherNum") String teacherNum,@Param("questionIds")  String questionIds,@Param("studentIds")  String studentIds);

    @Select("<script>" +
        "  select *  from schooltest.suggestion where  studentIds LIKE #{studentNum} " +
        "  <if test='createDate != null'>" +
        "   AND DATE(createTime) = #{createDate}" +
        "  </if>" +
        "  </script>         ")
    List<Suggestion> suggestionSearch(@Param("studentNum") String studentNum, @Param("teacherNum") String teacherNum, @Param("createDate")LocalDate createDate);

    @Select("<script>" +
            "  select a.*, b.createTime from schooltest.question a " +
            " LEFT JOIN schooltest.suggestion b " +
            "  ON FIND_IN_SET(a.id, b.questionIds) > 0 " +
            "  where b.id = ${id}" +
            "  </script> ")
    List<QuestionSuggestionVO> findQuestionsBySIds(@Param("id") Integer id);

    @Select("<script>" +
            " SELECT * FROM schooltest.question " +
            " WHERE grade = #{grade} " +
            "  AND subject = #{subject} " +
            "  AND difficulty IN (${difficulty}, ${difficulty1}, ${difficulty2}) " +
            "  <if test='questionTypes != null and !questionTypes.isEmpty()'>" +
            "    AND (" +
            "    <foreach item='type' collection='questionTypes' separator=' OR '>" +
            "      CONCAT(',', questionType, ',') LIKE CONCAT('%,', #{type}, ',%')" +
            "    </foreach>)" +
            "  </if>" +
            "ORDER BY RAND() LIMIT 3" +
            "</script>")
    List<Question> findQuestionsRandom(
            @Param("grade") String grade,
            @Param("subject") String subject,
            @Param("questionTypes") List<String> questionTypes,
            @Param("difficulty") Integer difficulty,
            @Param("difficulty1") Integer difficulty1,
            @Param("difficulty2") Integer difficulty2
    );

    @Update("<script>" +
            " UPDATE schooltest.question " +
            " <set>" +
            "  <if test='grade != null'>grade = #{grade},</if>" +
            "  <if test='subject != null'>subject = #{subject},</if>" +
            "  <if test='questionType != null'>questionType = #{questionType},</if>" +
            "  <if test='difficulty != null'>difficulty = #{difficulty},</if>" +
            "</set>" +
            " WHERE id = #{id}" +
            "</script>")
    Integer updateQuestion(@Param("id") Integer id, @Param("grade") String grade, @Param("subject") String subject, @Param("questionType") String questionType, @Param("difficulty") Integer difficulty);


    @Select("SELECT DISTINCT grade, subject, questionType FROM schooltest.question WHERE id IN ${id}")
    List<QuestionType> findQuestionTypeById(String s);

    @Select("<script>" +
            " SELECT url FROM schooltest.question " +
            " WHERE grade = #{questionType.grade} " +
            " AND subject = #{questionType.subject} " +
            " AND questionType = #{questionType.questionType} " +
            " ORDER BY RAND() LIMIT ${suggestionNum}" +
            "</script>")
    List<String> findUrlsByIdWithSuggestion(@Param("questionType") QuestionType questionType, @Param("suggestionNum") Integer suggestionNum);

    @Insert({
            "<script>",
            "INSERT INTO schooltest.question (testId, pageNum, sortNum, testType, grade, subject, questionType, url, difficulty)",
            "SELECT testId, pageNum, sortNum, testType, grade, subject, questionType, url, difficulty",
            "FROM schooltest.image",
            "WHERE id = #{imageId}",
            "AND NOT EXISTS (SELECT 1 FROM schooltest.question WHERE url = schooltest.image.url)",
            "</script>"
    })
    Integer saveQuestionWithImageId(@Param("imageId") Integer imageId);

    @Select("<script>" +
            " SELECT * FROM schooltest.suggestion " +
            " WHERE createTime &gt;= #{startDate} " +
            "  AND createTime &lt;= #{endDate} " +
            "  <if test='studentNums != null and !studentNums.isEmpty()'>" +
            "     AND (" +
            "    <foreach item='num' collection='studentNums' separator=' OR '>" +
            "      studentIds LIKE CONCAT('%', #{num}, '%')" +
            "    </foreach>)" +
            "  </if>" +
            "</script>")
    List<Suggestion> suggestionSearchAll(@Param("studentNums") List<String> studentNums, @Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate);
}
